import { defineEventHandler, readBody } from 'h3'
import { getPool } from '../util/mysql'
import { recordLog } from '../util/log'

// helper to format a parameterized SQL for logging (replaces ? with escaped params)
function formatSql(sql: string, params: any[] = []) {
  let idx = 0
  return sql.replace(/\?/g, () => {
    const val = params[idx++]
    if (val === null || val === undefined) return 'NULL'
    if (typeof val === 'number' || typeof val === 'bigint') return String(val)
    if (typeof val === 'boolean') return val ? '1' : '0'
    // handle Date-like strings or Date objects
    if (val instanceof Date) return `'${val.toISOString().slice(0, 19).replace('T', ' ')}'`
    // for safety, convert to string and escape single quotes
    return `'${String(val).replace(/'/g, "''")}'`
  })
}

export default defineEventHandler(async (event: any) => {
  const body = await readBody(event).catch(() => ({}))
  const params = (event.context && event.context.params) || {}
  const rawId = body.orderId ?? body.id ?? body.payment_id ?? params.OrderId ?? params.orderId ?? params.orderid

  if (!rawId) {
    event.node.res.statusCode = 400
    await recordLog(event as any, { message: 'paycheck2: missing order id', userId: 0 })
    return '未获取到订单号'
  }

  const orderId = Number(rawId)
  if (Number.isNaN(orderId)) {
    event.node.res.statusCode = 400
    await recordLog(event as any, { message: `paycheck2: invalid order id ${rawId}`, userId: 0 })
    return '未获取到订单号'
  }

  try {
    const pool = await getPool()

    const orderSql = `SELECT * FROM lin_order WHERE payment_id = ? AND status = ? AND is_deleted = 0 LIMIT 1`
    const orderParams = [orderId, '未支付']
    const [orderRows]: any = await pool.execute(orderSql, orderParams)

    if (!Array.isArray(orderRows) || orderRows.length === 0) {
      await recordLog(event as any, { message: `paycheck2: order not found orderId=${orderId}`, userId: 0 })
      return '未获取到订单号'
    }

    const order = orderRows[0]

    const paySql = `SELECT * FROM lin_payinfo WHERE message LIKE ? AND message LIKE ? AND create_time > ? AND create_time <= DATE_ADD(?, INTERVAL 5 MINUTE) AND create_time > DATE_SUB(NOW(3), INTERVAL 5 MINUTE) AND is_deleted = 0 ORDER BY create_time DESC LIMIT 1`
    const payParams = ['%微信支付收款%', `%${order.amount}%`, order.create_time, order.create_time]
    const [payRows]: any = await pool.execute(paySql, payParams)

    if (Array.isArray(payRows) && payRows.length > 0) {
      const pay = payRows[0]
  const updPaySql = `UPDATE lin_payinfo SET is_deleted = 1 WHERE id = ?`
  const updPayParams = [pay.id]
  await pool.execute(updPaySql, updPayParams)
    // 尝试从 lin_product 表获取 product_info，并写回到 lin_order.product_info
    let productInfo = order.product_info || ''
    try {
      const prodSql = `SELECT product_info FROM lin_product WHERE id = ? AND is_deleted = 0 LIMIT 1`
      const prodParams = [order.product_id]
      const [prodRows]: any = await pool.execute(prodSql, prodParams)
      if (Array.isArray(prodRows) && prodRows.length > 0) {
        productInfo = prodRows[0].product_info || productInfo
      }
    } catch (e: any) {
      // 如果查询产品信息失败，不影响支付流程，但记录日志
      await recordLog(event as any, { message: `paycheck2: failed to fetch product_info for product_id=${order.product_id} err=${String(e?.message || e)}`, userId: order.user_id || 0 })
    }

    const updOrderSql = `UPDATE lin_order SET status = ?, product_info = ? WHERE id = ?`
    const updOrderParams = ['已支付', productInfo, order.id]
    await pool.execute(updOrderSql, updOrderParams)
      await recordLog(event as any, { message: `paycheck2: payment success orderId=${orderId} payId=${pay.id}`, userId: order.user_id || 0 })
      return '支付成功'
    }

    await recordLog(event as any, { message: `paycheck2: payment pending orderId=${orderId}`, userId: order.user_id || 0 })
    return '支付中'
  } catch (err: any) {
    event.node.res.statusCode = 500
    await recordLog(event as any, { message: `paycheck2: internal error ${String(err?.message || err)}`, userId: 0 })
    return { status: 500, message: 'internal error', error: String(err?.message || err) }
  }
})
